const { Data, Cc } = require('@icon-park/vue')
var express = require('express')
var router = express.Router()

const { getsqlall, getone, getfun } = require('../db')
/* GET users listing. */
router.get('/', async (req, res) => {
  //解析请求参数
  const { page = 1, size = 5 } = req.query

  let sql1 = `select count(*) as total from xzd_users`
  let { total } = await getone(sql1)
  console.log(total)
  //定义偏移量
  let offset = (page - 1) * size
  //操作数据库
  let sql = `select id,username from xzd_users order by id desc limit ${offset},${size}`
  const data = await getsqlall(sql)
  res.send({
    code: 0,
    message: '获取所有用户成功',
    result: {
      total,
      data,
    },
  })
})
//搜索用户(模糊搜索)
router.get('/search', async (req, res) => {
  //解析请求参数
  const { keywords, page, size } = req.query

  //定义total
  let gettotal = `select count(*) as total from xzd_users where username REGEXP '1' `
  let { total } = await getone(gettotal)
  console.log(total)

  //定义偏移量
  const offset = (page - 1) * size
  //编写SQL语句
  let sql = `select * from xzd_users where username REGEXP '1' limit 1,3`
  console.log(sql)
  //执行sql语句返回结果
  const data = await getsqlall(sql)
  res.send({
    code: 0,
    message: '搜索用户成功',
    result: {
      data,
      total,
    },
  })
})

// 模糊搜索
// router.get('/search', async (req, res) => {
//   const { page, size, keywords } = req.query
//   let sql = `select count(*) as total from xzd_users where username REGEXP '${keywords}' `

//   const { total } = await getone(sql)

//   console.log(total)
//   sql = `select * from xzd_users where username REGEXP '${keywords}' limit ${
//     (page - 1) * size
//   },${size}`
//   const data = await getfun(sql)
//   res.send({
//     code: 0,
//     message: '搜索成功',
//     result: {
//       total,
//       data,
//     },
//   })
// })

/**
 * 根据id获取单个用户数据
 *GET/user/ids
 */
router.get('/:id', async (req, res) => {
  const { id } = req.params

  let sql = `select * from xzd_users where id=${id}`
  const data = await getone(sql)
  res.send({
    code: 0,
    message: '获取单个用户成功',
    result: data,
  })
})
//POST
router.post('/', async (req, res) => {
  const { username, password } = req.body

  let sql = `insert into xzd_users(username,password) values ('${username}','${password}')`
  const { insertID } = await getfun(sql)
  res.send({
    code: 0,
    message: '添加用户成功',
    result: {
      id: insertID,
      username,
      password,
    },
  })
})
//put:更新用户
// put users/:id
router.put('/:id', async (req, res) => {
  const { id } = req.params
  const { username, password } = req.body
  // console.log(req)
  // return

  let sql = `update xzd_users set username=${username},password=${password} where id = ${id}`

  const { insertID } = await getfun(sql)

  res.send({
    code: 0,
    message: '更新用户成功',
    result: {
      id: insertID,
      username,
      password,
    },
  })
})
//删除delete
router.delete('/:id', async (req, res) => {
  const { id } = req.params
  let sql = `delete from xzd_users where id = ${id}`
  await getfun(sql)
  res.send({
    code: 0,
    message: '删除用户成功',
    result: '',
  })
})

module.exports = router
